﻿using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChinaDynastyTours.Models;
using System;

namespace ChinaDynastyTours.Controls
{
    class TrainOrderCtrl
    {
        #region 获取火车相关信息
        /// <summary>
        /// 获取火车预定表数据
        /// </summary>
        /// <param name="tourGroupScheduleId">行程计划ID,每条行程计划对应唯一一条宾馆数据，反过来则不成立</param>
        /// <param name="expenseId">费用标识</param>
        /// <returns></returns>
        internal static DataTable GetTrainOrderData(int tourGroupScheduleId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT t.[TrainOrderID], t.[ExpenseID], t.[TourgroupID], t.[TourgroupName], t.[TourGroupScheduleID], t.[TrainCode], t.[StartDate], 
                                                t.[StartTime], t.[EndDate], t.[EndTime], t.[BeginPosition], t.[EndPosition], t.[HardSleeperTopQuantity], t.[HardSleeperMiddleQuantity], 
                                                t.[HardSleeperBottomQuantity], t.[SoftSleeperTopQuantity], t.[SoftSleeperBottomQuantity], t.[HighBoxQuantity], t.[HardSeatQuantity], 
                                                t.[SoftSeatQuantity], t.[ChildSeatQuantity], t.[HardSleeperTopPrice], t.[HardSleeperMiddlePrice], t.[HardSleeperBottomPrice], 
                                                t.[SoftSleeperTopPrice],t.[ChildSeatPrice],t.[SoftSleeperBottomPrice],t.[HighBoxPrice],t.[HardSeatPrice],t.[SoftSeatPrice],t.[HandlingCharge],
                                                t.[Remark],t.[AccompanyNum],t.[AdultNum],t.[ChildNum],t.[OrderState],t.[Seat],
                                                e.[TypeBelong],e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget],e.[ServiceName],e.[PayTotal],e.[Remark]as ExpenseRemark  
                                                FROM [fee_TrainOrder] t,fee_Expense e WHERE  t.ExpenseID=e.ExpenseID AND t.TourGroupScheduleID={0}", tourGroupScheduleId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 火车及火车预订相关信息的增、删、改

        /// <summary>
        /// 创建飞机预订及费用数据
        /// </summary>
        /// <param name="expense"></param>
        /// <param name="flightOrder"></param>
        /// <returns></returns>
        internal static int CreateTrainOrderAndExpenseData(ExpenseEntity expense, TrainOrderEntity trainOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],[PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[CreateTime],[ExpenseTypeID],[ExpenseType])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}',{16},'{17}');Select SCOPE_IDENTITY();",
                                        expense.TourgroupID,
                                        expense.ProviderID,
                                        expense.ProviderName,
                                        expense.ServiceName,
                                        expense.Budget,
                                        expense.PayTotal,
                                        expense.ApplyPayTotal,
                                        expense.AdjustAmount,
                                        expense.Status,
                                        expense.TypeBelong,
                                        expense.ApplyPay,
                                        expense.PayStatus,
                                        expense.TourgroupName,
                                        expense.CreateByUserName,
                                        expense.TourGroupScheduleID,
                                        expense.CreateTime,
                                        expense.ExpenseTypeID,
                                        expense.ExpenseType);
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            string sqlFlight = string.Format(@"INSERT INTO fee_TrainOrder ( [ExpenseID], [TourgroupID], [TourgroupName], [TourGroupScheduleID], [TrainCode],
                                                                            [StartTime], [EndTime], [BeginPosition], [EndPosition],
                                                                            [HardSleeperTopQuantity], [HardSleeperMiddleQuantity], [HardSleeperBottomQuantity],
                                                                            [SoftSleeperTopQuantity], [SoftSleeperBottomQuantity], [HighBoxQuantity], [HardSeatQuantity],
                                                                            [SoftSeatQuantity], [HardSleeperTopPrice], [HardSleeperMiddlePrice], [HardSleeperBottomPrice],
                                                                            [SoftSleeperTopPrice], [SoftSleeperBottomPrice], [HighBoxPrice], [HardSeatPrice], [SoftSeatPrice], 
                                                                            [HandlingCharge], [Remark],[StartDate],[EndDate],[OrderState],[AccompanyNum],[AdultNum],[ChildNum],[ChildSeatQuantity],[ChildSeatPrice],[ProviderID],[ProviderName],[Seat])
                                                                            VALUES({0},'{1}','{2}',{3},'{4}','{5}','{6}','{7}','{8}',{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},'{26}','{27}','{28}','{29}',{30},{31},{32},{33},{34},{35},'{36}','{37}')",
                                                                           expenseID,
                                                                           expense.TourgroupID,
                                                                           trainOrder.TourgroupName,
                                                                           trainOrder.TourGroupScheduleID,
                                                                           trainOrder.TrainCode,
                                                                           trainOrder.StartTime,
                                                                           trainOrder.EndTime,
                                                                           trainOrder.BeginPosition,
                                                                           trainOrder.EndPosition,
                                                                           trainOrder.HardSleeperTopQuantity,
                                                                           trainOrder.HardSleeperMiddleQuantity,
                                                                           trainOrder.HardSleeperBottomQuantity,
                                                                           trainOrder.SoftSleeperTopQuantity,
                                                                           trainOrder.SoftSleeperBottomQuantity,
                                                                           trainOrder.HighBoxQuantity,
                                                                           trainOrder.HardSeatQuantity,
                                                                           trainOrder.SoftSeatQuantity,
                                                                           trainOrder.HardSleeperTopPrice,
                                                                           trainOrder.HardSleeperMiddlePrice,
                                                                           trainOrder.HardSleeperBottomPrice,
                                                                           trainOrder.SoftSleeperTopPrice,
                                                                           trainOrder.SoftSleeperBottomPrice,
                                                                           trainOrder.HighBoxPrice,
                                                                           trainOrder.HardSeatPrice,
                                                                           trainOrder.SoftSeatPrice,
                                                                           trainOrder.HandlingCharge,
                                                                           trainOrder.Remark,
                                                                           trainOrder.StartDate,
                                                                           trainOrder.EndDate,
                                                                           trainOrder.OrderState,
                                                                           trainOrder.AccompanyNum,
                                                                           trainOrder.AdultNum,
                                                                           trainOrder.ChildNum,
                                                                           trainOrder.ChildSeatQuantity,
                                                                           trainOrder.ChildSeatPrice,
                                                                           trainOrder.ProviderID,
                                                                           trainOrder.ProviderName,
                                                                           trainOrder.Seat
                                                                           );
            return db.ExecuteNonQuery(CommandType.Text, sqlFlight);
        }
        /// <summary>
        /// 计调更新飞机预订的部分数据信息，不包括(TourgroupID,TourgroupName, OrderState,AccompanyNum,AdultNum,ChildNum)字段
        /// </summary>                                                                                                             
        /// <param name="expense"></param>                                                                                        
        /// <param name="flightOrder"></param>                                                                                     
        /// <returns></returns>                                                                                                    
        internal static int UpdateTrainOrderData(ExpenseEntity expense, TrainOrderEntity trainOrder)                               
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ProviderID]={0},
                                        [ProviderName]='{1}',
                                        [ServiceName]='{2}',
                                        [Budget]={3}
                                        WHERE ExpenseID={4}",
                                       expense.ProviderID,
                                       expense.ProviderName,
                                       expense.ServiceName,
                                       expense.Budget,
                                       expense.ExpenseID);

            db.ExecuteNonQuery(CommandType.Text, sqlExpense);
            string sqlTrain = string.Format(@"UPDATE fee_TrainOrder SET [TrainCode]='{0}', [StartTime]='{1}', [EndTime]='{2}',
                                                                         [BeginPosition]='{3}', [EndPosition]='{4}',[HardSleeperTopQuantity]={5},
                                                                         [HardSleeperMiddleQuantity]={6},[HardSleeperBottomQuantity]={7}, [SoftSleeperTopQuantity]={8}, 
                                                                         [SoftSleeperBottomQuantity]={9},[HighBoxQuantity]={10},[HardSeatQuantity]={11},[SoftSeatQuantity]={12},
                                                                         [HardSleeperTopPrice]={13},[HardSleeperMiddlePrice]={14},[HardSleeperBottomPrice]={15},
                                                                         [SoftSleeperTopPrice]={16},[SoftSleeperBottomPrice]={17},[HighBoxPrice]={18}, [HardSeatPrice]={19},
                                                                         [SoftSeatPrice]={20},[HandlingCharge]={21},[Remark]='{22}',[StartDate]='{23}',[EndDate]='{24}',
                                                                         [ChildSeatQuantity]={25},[ChildSeatPrice]={26},[ProviderID]={27},[ProviderName]='{28}',
                                                                         [AccompanyNum]={29},[AdultNum]={30},[ChildNum]={31},[Seat]='{32}' 
                                                                          WHERE TrainOrderID={33}",//[TourgroupID]='{0}', [TourgroupName]='{1}',[OrderState]='{27}',
                                                                        trainOrder.TrainCode,
                                                                        trainOrder.StartTime,
                                                                        trainOrder.EndTime,
                                                                        trainOrder.BeginPosition,
                                                                        trainOrder.EndPosition,
                                                                        trainOrder.HardSleeperTopQuantity,
                                                                        trainOrder.HardSleeperMiddleQuantity,
                                                                        trainOrder.HardSleeperBottomQuantity,
                                                                        trainOrder.SoftSleeperTopQuantity,
                                                                        trainOrder.SoftSleeperBottomQuantity,
                                                                        trainOrder.HighBoxQuantity,
                                                                        trainOrder.HardSeatQuantity,
                                                                        trainOrder.SoftSeatQuantity,
                                                                        trainOrder.HardSleeperTopPrice,
                                                                        trainOrder.HardSleeperMiddlePrice,
                                                                        trainOrder.HardSleeperBottomPrice,
                                                                        trainOrder.SoftSleeperTopPrice,
                                                                        trainOrder.SoftSleeperBottomPrice,
                                                                        trainOrder.HighBoxPrice,
                                                                        trainOrder.HardSeatPrice,
                                                                        trainOrder.SoftSeatPrice,
                                                                        trainOrder.HandlingCharge,
                                                                        trainOrder.Remark,
                                                                        trainOrder.StartDate,
                                                                        trainOrder.EndDate,
                                                                        trainOrder.ChildSeatQuantity,
                                                                        trainOrder.ChildSeatPrice,
                                                                        trainOrder.ProviderID,
                                                                        trainOrder.ProviderName,
                                                                        trainOrder.AccompanyNum,
                                                                        trainOrder.AdultNum,
                                                                        trainOrder.ChildNum,
                                                                         trainOrder.Seat,
                                                                        trainOrder.TrainOrderID
                                                                        //trainOrder.TourgroupID,
                                                                        //trainOrder.TourgroupName,
                                                                        //trainOrder.OrderState,
                                                                        );

            return db.ExecuteNonQuery(CommandType.Text, sqlTrain);
        }
        /// <summary>
        /// 更改火车的订单状态
        /// </summary>
        /// <param name="trainOrderId">订单号</param>
        /// <param name="orderState">状态</param>
        /// <returns></returns>
        internal static int UpdateTrainOrderStateData(string trainOrderId, OrderStateEnum orderState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlTrain = string.Format(@"UPDATE fee_TrainOrder SET [OrderState]='{0}' WHERE TrainOrderID={1}", orderState, trainOrderId);

            return db.ExecuteNonQuery(CommandType.Text, sqlTrain);
        }

        /// <summary>
        /// 删除指定行程计划号的火车预订及费用信息
        /// </summary>
        /// <param name="scheduleID"></param>
        /// <returns></returns>
        internal static int DeleteTrainOrder(int orderId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("select expenseid from fee_TrainOrder where TrainOrderID={0}", orderId);
            DbCommand dbCmd = db.GetSqlStringCommand(sql);

            object expenseObj = db.ExecuteScalar(dbCmd);

            if (expenseObj == null)
                return 0;

            sql = string.Format(@"DELETE FROM fee_TrainOrder WHERE TrainOrderID={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和相关预订内容
                                         orderId,
                                         expenseObj.ToString());
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }


        #endregion

        #region 火车计调查询

        internal static DataTable GetTrainOrderData(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT IDENTITY(INT,1,1) as SeqID,[TourGroupScheduleID], [TrainCode], [StartDate],[StartTime], [EndDate], [EndTime], 
                                        [BeginPosition], [EndPosition], [AdultNum],[ChildNum],[Seat],[ProviderID] INTO #TmpDT
                                        FROM [fee_TrainOrder] WHERE TourgroupID='{0}';
                                        SELECT * FROM #TmpDT;
                                        DROP TABLE #TmpDT;", tourGroupId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetTrainOrderData(string tourGroupId, string providerId, DateTime startDate, DateTime endDate, OrderStateEnum orderState)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘火车预定信息’表联合数据

            string szCondition = "";

            if (tourGroupId!= "")
                szCondition = "t.TourGroupID='" + tourGroupId + "'";
            if (szCondition != "")
            {
                if (providerId != "")
                    szCondition += " AND e.ProviderID='" + providerId + "'";
            }
            else
            {
                if (providerId != "")
                    szCondition += " e.ProviderID='" + providerId + "'";
            }
            if (szCondition != "")
            {
                if (orderState.ToString() == "全部")
                    szCondition += string.Format(" AND t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                else
                    szCondition += string.Format(" AND t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.OrderState='{2}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }
            else
            {
                if (orderState.ToString() == "全部")
                    szCondition += string.Format(" t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                else
                    szCondition += string.Format(" t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.OrderState='{2}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }



            string Sql = @"SELECT t.[TrainOrderID], t.[ExpenseID], t.[TourgroupID], t.[TourgroupName], t.[TourGroupScheduleID], t.[TrainCode], t.[StartDate], 
                        t.[StartTime], t.[EndDate], t.[EndTime], t.[BeginPosition], t.[EndPosition], t.[HardSleeperTopQuantity], t.[HardSleeperMiddleQuantity], 
                        t.[HardSleeperBottomQuantity], t.[SoftSleeperTopQuantity], t.[SoftSleeperBottomQuantity], t.[HighBoxQuantity], t.[HardSeatQuantity], 
                        t.[SoftSeatQuantity], t.[ChildSeatQuantity], t.[HardSleeperTopPrice], t.[HardSleeperMiddlePrice], t.[HardSleeperBottomPrice], 
                        t.[SoftSleeperTopPrice],t.[ChildSeatPrice],t.[SoftSleeperBottomPrice],t.[HighBoxPrice],t.[HardSeatPrice],t.[SoftSeatPrice],t.[HandlingCharge],
                        t.[Remark],t.[AccompanyNum],t.[AdultNum],t.[ChildNum],t.[OrderState],t.[Seat],e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget],e.[PayStatus],e.[ApplyPayTotal],e.[PayTotal]
                        FROM [fee_TrainOrder] t,fee_Expense e ";
            
            if (szCondition != "")
                Sql += " WHERE " + szCondition;


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetTrainOrderDataByExpenseId(string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT t.[TrainOrderID], t.[ExpenseID], t.[TourgroupID], t.[TourgroupName], t.[TourGroupScheduleID], t.[TrainCode], t.[StartDate], t.[Seat],
                        t.[StartTime], t.[EndDate], t.[EndTime], t.[BeginPosition], t.[EndPosition], t.[HardSleeperTopQuantity], t.[HardSleeperMiddleQuantity], 
                        t.[HardSleeperBottomQuantity], t.[SoftSleeperTopQuantity], t.[SoftSleeperBottomQuantity], t.[HighBoxQuantity], t.[HardSeatQuantity], 
                        t.[SoftSeatQuantity], t.[ChildSeatQuantity], t.[HardSleeperTopPrice], t.[HardSleeperMiddlePrice], t.[HardSleeperBottomPrice], 
                        t.[SoftSleeperTopPrice],t.[ChildSeatPrice],t.[SoftSleeperBottomPrice],t.[HighBoxPrice],t.[HardSeatPrice],t.[SoftSeatPrice],t.[HandlingCharge],
                        t.[Remark],t.[AccompanyNum],t.[AdultNum],t.[ChildNum],t.[OrderState],e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget]
                        FROM [fee_TrainOrder] t,fee_Expense e WHERE e.ExpenseID=t.ExpenseID AND e.ExpenseID={0} ",expenseId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

    }
}
